package com.hnkywl.sts.dao.report;

import com.hnkywl.sts.dao.base.RegionDao;
import com.hnkywl.sts.dao.base.RegionForBgDao;
import com.hnkywl.sts.entity.sfgl.Sfk;
import net.ruixin.orm.hibernate.Page;
import net.ruixin.orm.hibernate.SimpleHibernateTemplate;
import oracle.jdbc.OracleCallableStatement;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.SQLQuery;
import org.hibernate.transform.AliasToEntityMapResultTransformer;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.sql.*;
import java.util.*;
import java.util.Date;

/**
 * Created with IntelliJ IDEA.
 * User: Administrator
 * Date: 13-6-15
 * Time: 下午9:13
 * To change this template use File | Settings | File Templates.
 */
@Repository
public class FkhzjbDao extends SimpleHibernateTemplate {

    public Page<Map> findfkhzjbList(String gsMc, String khMc, int pageNo, int pageSize) {
        Page<Map> page = new Page<Map>();
        Map<String, Object> param = new HashMap<String, Object>();
        List<String> condition = new ArrayList<String>();
        String sqlColumn = " select gs.qc as \"yysqc\",kh.qc as \"khqc\",qyfr.khh as \"khh\" ," +
                " sum(sf.sfje) as \"czje\"," +
                " (SELECT nvl(sum(SFK.sfje),0)\n" +
                "          FROM T_BIZ_SFK SFK\n" +
                "         WHERE SFK.KXSY_SJZDZ_ID = 120591\n" +
                "           AND KH.ID = SFK.GLDW_ID AND SFK.Sfhz=0) AS \"hk\",\n" +
                " (SELECT nvl(sum(SFK.sfje),0)\n" +
                "          FROM T_BIZ_SFK SFK\n" +
                "         WHERE SFK.KXSY_SJZDZ_ID = 121191\n" +
                "           AND KH.ID = SFK.GLDW_ID) AS \"htbzj\",sum(ye) as \"zjye\"," +
                " (SELECT nvl(sum(SFK.YE),0)\n" +
                "          FROM T_BIZ_SFK SFK\n" +
                "         WHERE SFK.KXSY_SJZDZ_ID = 120591\n" +
                "           AND KH.ID = SFK.GLDW_ID AND SFK.Sfhz=0) AS \"hkye\",\n" +
                " (SELECT nvl(sum(SFK.YE),0)\n" +
                "          FROM T_BIZ_SFK SFK\n" +
                "         WHERE SFK.KXSY_SJZDZ_ID = 121191\n" +
                "           AND KH.ID = SFK.GLDW_ID) AS \"htbzjye\"," +
                " kh.id as \"khid\" " +
                " from t_biz_sfk sf , t_sys_gsjg gs ,t_sys_gsjg kh ,t_sys_qyfr qyfr where sf.sbgs_id = gs.id and " +
                " sf.gldw_id = kh.id  and kh.qyfr_id = qyfr.id and sf.deleted = 0 and kh.deleted = 0 and sf.sfhz = 0  ";
        String groupSql = " group by gs.qc,kh.qc,qyfr.khh,kh.id order by kh.qc  ";
        if (StringUtils.isNotEmpty(gsMc)) {
            condition.add(" gs.qc like :gsMc ");
            param.put("gsMc", "%" + gsMc + "%");
        }
        if (StringUtils.isNotEmpty(khMc)) {
            condition.add(" kh.qc like :khMc ");
            param.put("khMc", "%" + khMc + "%");
        }
        String whereSql = "";
        if (!condition.isEmpty())
            whereSql = StringUtils.join(condition, " and ");
        String countSql = "select count(*) from (" + sqlColumn + whereSql + groupSql + " )";
        String resultSql = sqlColumn + whereSql + groupSql;
        BigDecimal count = (BigDecimal) createSQLQuery(countSql).setProperties(param).uniqueResult();
        page.setTotalCount(count.intValue());
        if (count.intValue() != 0) {
            List<Map> list = createSQLQuery(resultSql)
                    .setProperties(param)
                    .setFirstResult((pageNo - 1) * pageSize)
                    .setMaxResults(pageSize)
                    .setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP)
                    .list();
            page.setResult(list);
        } else {
            page.setResult(new ArrayList<Map>());
        }
        return page;
    }

    /**
     * @param khId
     * @param kxsy
     * @param pageNo
     * @param pageSize
     * @return
     */
    public Page<Sfk> findSklxListByKhId(Long khId, Long kxsy, int pageNo, int pageSize) {
        String hql;
        List<Object> param = new ArrayList<Object>();
        StringBuilder condition = new StringBuilder();
        Page<Sfk> page = new Page<Sfk>(pageNo, pageSize, 0);
        //==============条件拼接===============
        if (khId != null) {
            if (condition.length() != 0) {
                condition.append(" and gldw.id = ?");
                param.add(khId);
            } else {
                condition.append("gldw.id = ?");
                param.add(khId);
            }
        }
        if (kxsy != null) {
            if (condition.length() != 0) {
                condition.append(" and kxsy.id = ?");
                param.add(kxsy);
            } else {
                condition.append("kxsy.id = ?");
                param.add(kxsy);
            }
        }
        //==============记录数量===============
        hql = "select count(*) from Sfk where sfklx in (3,4) " +
                " and (createUser.id is not null )  and nbhzsfk is null "
                + RegionDao.createDataRegion("and");
        if (condition.length() != 0)
            hql = hql + " and " + condition;
        Long c = null;
        if (param.size() != 0) {
            c = (Long) findUnique(hql, param.toArray());
            page.setTotalCount(c.intValue());
        } else {
            c = (Long) findUnique(hql);
            page.setTotalCount(c.intValue());
        }
        if (c == 0)
            return page;
        //==============查询结果===============
        hql = "from Sfk where sfklx in (3,4) " +

                " and nbhzsfk is null and (createUser.id is not null ) "

                + RegionDao.createDataRegion("and");
        if (condition.length() != 0)
            hql = hql + " and " + condition;
        hql = hql + " order by last_updated desc";
        if (param.size() != 0)
            return find(page, hql, param.toArray());
        return find(page, hql);
    }
}
